<?php
/**
 * CMysqlSchema class file.
 * 获取Mysql表的信息, 包含表的主键和外键, 列的情况.
 * @author Huan Song <iphper@yeah.net>
 * @version $Id: CMysqlSchema.php 1 2011-06-07 18:36:18Z huan.song $
 * @package system.db.mysql
 * @since 2.0
 */
class CMysqlSchema extends CApplication implements IDbSchema 
{
	/**
	 * 收集表的信息.
	 * @param string 表名.
	 * @return CMysqlTableSchema $table. 如果表不存在, 返回null.
	 */
	public function loadTable($name)
	{
		$table = new CMysqlTableSchema();
		$table->name = $name;
		$table->rawName = $this->quoteTableName($name);
		
		if ($this->findColumns($table))
		{
			$this->findConstraints($table);
			return $table;
		}
		else
			return null;
	}
	
	/**
	 * 引用一个表名, 被引用的表名可以放在SQL语句中执行.
	 * @param string $name 表名.
	 * @return string 被引用的表名.
	 */
	public function quoteTableName($name)
	{
		return '`' . $name . '`';
	}
	
	/**
	 * 引用一个列名, 被引用的列名可以放在SQL语句中执行.
	 * @param string $name 列名.
	 * @return string 被引用的列名.
	 */
	public function quoteColumnName($name)
	{
		return '`' . $name . '`';
	}
	
	/**
	 * 引用多个个列名, 被引用的列名可以放在SQL语句中执行.
	 * @param string $name 列名.
	 * @return string 被引用的列名.
	 */
	public function quoteColumnNames(array $names)
	{
		return array_map(array($this, 'quoteColumnName'), $names);
	}
	
	/**
	 * 收集列的信息.
	 * @param CMysqlTableSchema $table.
	 * @return boolean 当前数据库中表是否存在.
	 */
	protected function findColumns(CMysqlTableSchema $table)
	{
		try 
		{
			$columns = $this->getDbStatement()->
				fetchAssocBySql('SHOW COLUMNS FROM ' . $table->name);
		}
		catch (Exception $e) 
		{
			return false;
		}
		
		foreach ($columns as $column)
		{
			$c = $this->createColumn($column);
			
			if ($c->isPrimaryKey)
			{
				if (null === $table->primaryKey)
				{
					$table->primaryKey = $c->name;
					$table->rawPrimaryKey = $this->quoteColumnName($c->name);
				}	
			}
			else
			{
				if (null !== $c->defaultValue)
					$table->attributeDefaults[$c->name] = $c->defaultValue;
			}
			
			$table->selectFields[] = $c->rawName;
			$table->columns[$c->name] = $c;
		}
		
		return true;
	}
	
	/**
	 * 收集表的外键.
	 * @param CMysqlTableSchema $table.
	 */
	protected function findConstraints(CMysqlTableSchema $table)
	{
		$sql = $this->getDbStatement()->
				fetchColumnOneBySql('SHOW CREATE TABLE ' . $table->name, null, 1);
		$sql = str_replace('`', '', $sql);
		
		$matches = array();
		$regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
		preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER);
		
		foreach ($matches as $match)
		{
			$keys = array_map('trim', explode(',', $match[1]));
			$fks = array_map('trim', explode(',', $match[3]));
			foreach ($keys as $k => $name)
			{
				$table->foreignKeys[$name] = array($match[2], $fks[$k]);
				if (isset($table->columns[$name]))
					$table->columns[$name]->isForeignKey = true;
			}
		}
	}
	
	/**
	 * 实例化一个Mysql表的列对象.
	 * @param array $column 列数据.
	 * @return CMysqlColumnSchema 列对象.
	 */
	protected function createColumn($column)
	{
		$c = new CMysqlColumnSchema();
		$c->name = $column['Field'];
		$c->rawName = $this->quoteColumnName($column['Field']);
		$c->allowNull = ($column['Null'] === 'YES');
		$c->isPrimaryKey = (false !== strpos($column['Key'], 'PRI'));
		$c->dbType = $column['Type'];
		$this->extractType($c);
		$this->extractSizeAndScale($c);
		$c->defaultValue = $c->typecast($column['Default']);
		$c->autoIncrement = (strpos(strtolower($column['Extra']), 'auto_increment') !== false);	
		return $c;
	}
	
	/**
	 * 从数据库类型取出PHP类型.
	 * @param CMysqlColumnSchema $c.
	 */
	protected function extractType(CMysqlColumnSchema $c)
	{
		if (stripos($c->dbType, 'int') !== false && stripos($c->dbType, 'unsigned int') === false)
			$c->type = 'integer';
		else if (stripos($c->dbType, 'bool') !== false)
			$c->type = 'boolean';
		else if (preg_match('/(real|floa|doub)/i', $c->dbType))
			$c->type = 'double';
		else
			$c->type = 'string';
	}
	
	/**
	 * 从数据库类型取出字段长度和精度.
	 * @param CMysqlColumnSchema $c.
	 */
	protected function extractSizeAndScale(CMysqlColumnSchema $c)
	{
		if (strpos($c->dbType, '(') && preg_match('/\((.*)\)/', $c->dbType, $matches))
		{
			$values = explode(',', $matches[1]);
			$c->size = (int) $values[0];
			if (isset($values[1]))
				$c->scale = (int) $values[1];
		}
	}
}